package com.fduss.artStore.dao.impl;

import com.fduss.artStore.dao.OrderDao;
import com.fduss.artStore.entity.Order;
import com.fduss.artStore.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by alex on 28/06/2017.
 */
public class OrderDaoImpl implements OrderDao {
    private JdbcUtil util;

    public OrderDaoImpl() {
        util = new JdbcUtil();
    }

    @Override
    public Order add(Order order) {
        Connection conn = util.getConnection();
        String sql = "INSERT INTO `art`.`order` " +
                "(`OrderID`, `CustomerID`, `DateCreated`, `DateCompleted`) " +
                "VALUES (?, ?, ?, ?)";
        PreparedStatement pst = null;
        boolean flag = false;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, order.getOrderId());
            pst.setInt(2, order.getCustomerId());
            pst.setTimestamp(3, order.getDateCreated());
            pst.setTimestamp(4, order.getDateCompleted());
            pst.executeUpdate();
            // get Id
            //language=MySQL
            String idSql = "SELECT MAX(OrderID) AS OrderID FROM `order`";
            pst = conn.prepareStatement(idSql);
            ResultSet rs = pst.executeQuery();
            if (rs.next())
                order.setOrderId(rs.getInt("OrderID"));
                flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }
        return flag ? order : null;
    }

    @Override
    public void delete(int orderId) {
        Connection conn = util.getConnection();
        String sql = "DELETE FROM `order` WHERE `OrderID` = ?";
        PreparedStatement pst = null;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, orderId);
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }
    }

    @Override
    public Order getOne(int orderId) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM `order` where `OrderID` = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Order order = null;

        try{
            pst  = conn.prepareStatement(sql);
            pst.setInt(1, orderId);
            rs = pst.executeQuery();

            if (rs.next()){
                order = buildOrder(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return order;
    }

    @Override
    public Order update(Order order) {
        Connection conn = util.getConnection();
        String sql = "UPDATE `art`.`order` SET " +
                "`CustomerID`=?, `DateCreated`=?, `DateCompleted`=? " +
                "WHERE `OrderID`=? ";

        PreparedStatement pst = null;
        boolean flag = false;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, order.getCustomerId());
            pst.setTimestamp(2, order.getDateCreated());
            pst.setTimestamp(3, order.getDateCompleted());
            pst.setInt(4, order.getOrderId());
            pst.executeUpdate();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }
        return flag ? order : null;
    }

    @Override
    public List<Order> getByCustomer(int customerId) {
        Connection conn = util.getConnection();
        String sql = "select * from `order` where `CustomerID` = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Order> order = new ArrayList<>();
        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, customerId);
            rs = pst.executeQuery();

            while (rs.next()){
                order.add(buildOrder(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return order;
    }

    @Override
    public List<Order> getAll() {
        Connection conn = util.getConnection();
        String sql = "select * from `order` ";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Order> order = new ArrayList<>();
        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();

            while (rs.next()){
                order.add(buildOrder(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return order;
    }

    @Override
    public List<Order> getByStateAndCustomer(int customerId,int state) {
        assert state == 0 || state == 1;
        Connection conn = util.getConnection();
        String[] sqls = {
                "select * from `order` WHERE CustomerID = ? AND DateCompleted IS NULL",
                "select * from `order` WHERE CustomerID = ? AND DateCompleted IS NOT NULL "
        };
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Order> order = new ArrayList<>();
        try{
            pst = conn.prepareStatement(sqls[state]);
            pst.setInt(1, customerId);
            rs = pst.executeQuery();
            while (rs.next()){
                order.add(buildOrder(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return order;
    }

    private Order buildOrder(ResultSet rs) throws SQLException {
        Order order = new Order();
        order.setCustomerId(rs.getInt("CustomerID"));
        order.setDateCompleted(rs.getTimestamp("DateCompleted"));
        order.setDateCreated(rs.getTimestamp("DateCreated"));
        order.setOrderId(rs.getInt("OrderID"));
        return order;
    }
}
